﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using System.Data.SqlClient;
using System.Data.OleDb;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ReceiveDAL : IReceiveDAL
    {
       
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ReceiveDAL()
        {

        }

        public ReceiveInfo GetRequestBySeq(int reqSeq)
        {
            string strSQL;

            RequestInfo requestInfo = new RequestInfo();
            ReceiveInfo req = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", reqSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT RQ.BGM_REQ_SEQ, RQ.BGM_REQ_CODE, RQ.BGM_REQ_NAME, RQ.BGM_REQ_DESCR, RQ.BGM_REQ_DATE, RQ.BGM_REQ_MONTH_START, RQ.BGM_REQ_MONTH_END, RQ.BGM_REQ_REASON, RQ.BGM_REQ_OBJECT, RQ.BGM_REQ_TARGET, RQ.BGM_REQ_PLAN, RQ.BGM_REQ_DTLS_1, RQ.BGM_REQ_REMARK, BGM_STRATEGY_SEQ, JOB_SITE_SEQ, BGM_YEAR, RECORD_STATUS"
                    + " FROM BGM_REQUEST RQ"
                    + " WHERE RQ.BGM_REQ_SEQ = @seq AND RQ.RECORD_STATUS not like @status";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);


                //while (rdr.Read())
                //{
                    //req = new RequestInfo(rdr.GetInt32(0), rdr.GetString(1), rdr.GetString(2), (rdr.IsDBNull(3)) ? "" : rdr.GetString(3), rdr.GetDateTime(4), (rdr.IsDBNull(5)) ? "" : rdr.GetString(5), (rdr.IsDBNull(6)) ? "" : rdr.GetString(6), (rdr.IsDBNull(7)) ? "" : rdr.GetString(7), (rdr.IsDBNull(8)) ? "" : rdr.GetString(8), (rdr.IsDBNull(9)) ? "" : rdr.GetString(9), (rdr.IsDBNull(10)) ? "" : rdr.GetString(10), (rdr.IsDBNull(11)) ? "" : rdr.GetString(11), (rdr.IsDBNull(12)) ? "" : rdr.GetString(12), (rdr.IsDBNull(13)) ? "" : rdr.GetString(13), (rdr.IsDBNull(14)) ? "" : rdr.GetString(14), rdr.GetInt32(15), (rdr.IsDBNull(16)) ? "" : rdr.GetString(16));

                    //requestInfo.ReqCode = rdr.GetString(0);
                    //requestInfo.ReqName = rdr.GetString(1);
                    //requestInfo.ReqDescr = rdr.GetString(2);
                    //requestInfo.ReqDate = rdr.GetDateTime(3);
                    //requestInfo.ReqDateStart = rdr.GetString(4);
                    //requestInfo.ReqDateEnd = rdr.GetString(5);
                    //requestInfo.ReqReason = rdr.GetString(6);
                    //requestInfo.ReqObject = rdr.GetString(7);
                    //requestInfo.ReqTarget = rdr.GetString(8);
                    //requestInfo.ReqPlan = rdr.GetString(9);
                    //requestInfo.ReqDtls_1 = rdr.GetString(10);
                    //requestInfo.ReqRemark = rdr.GetString(11);
                    //requestInfo.ReqStrategy = rdr.GetString(12);
                    //requestInfo.ReqJobSite = rdr.GetString(13);
                //}

                //return req;
                return new ReceiveInfo();
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<ReceiveInfo> GetReceiveByYear(int year)
        {
            List<ReceiveInfo> receiveInfo = new List<ReceiveInfo>();
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@year", year);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT BGM_RCV_SEQ, BGM_RCV_CODE, BGM_RCV_DESCR, BGM_YEAR"
                    + " FROM BGM_RECEIVE RC"
                    + " WHERE BGM_YEAR = @year"
                    + " AND RECORD_STATUS not like @status";

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ReceiveInfo receiveInfos = new ReceiveInfo();

                    receiveInfos.ReceiveSeq = Convert.ToInt32(dr["BGM_RCV_SEQ"]);
                    receiveInfos.ReceiveCode = Convert.ToString(dr["BGM_RCV_CODE"]);
                    receiveInfos.ReceiveDescr = Convert.ToString(dr["BGM_RCV_DESCR"]);
                    receiveInfos.BgmYear = Convert.ToInt32(dr["BGM_YEAR"]);

                    receiveInfo.Add(receiveInfos);
                }

                //while (rdr.Read())
                //{
                //    ReceiveInfo rcv = new ReceiveInfo(rdr.GetInt32(0), rdr.GetString(1), (rdr.IsDBNull(2)) ? "" : rdr.GetString(2), rdr.GetInt32(3));
                //    receiveInfo.Add(rcv);
                //}

                return receiveInfo;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public DataSet GetTotalBudgetByReqSeq(int reqSeq)
        {
            string strSQL = "SELECT S.BGM_SOURCE_SEQ, S.BGM_SOURCE_NAME, COALESCE(SUM(COALESCE(BGM_REQ_EXP_QTY,1) * BGM_REQ_EXP_PRICE), 0) AS TOTAL_REQ"
                + " FROM BGM_REQUEST_EXPENSE R"
                    + " LEFT JOIN BGM_REQUEST Q ON R.BGM_REQ_SEQ = Q.BGM_REQ_SEQ"
                    + " LEFT JOIN BGM_MAS_SOURCE S ON R.BGM_SOURCE_SEQ = S.BGM_SOURCE_SEQ"
                + " WHERE Q.BGM_REQ_SEQ = @seq"
                    + " AND Q.RECORD_STATUS not like @status"
                + " GROUP BY S.BGM_SOURCE_SEQ, S.BGM_SOURCE_NAME"
                + " UNION ALL"
                + " SELECT S.BGM_SOURCE_SEQ, S.BGM_SOURCE_NAME, 0 AS TOTAL_REQ"
                + " FROM BGM_MAS_SOURCE S"
                + " WHERE BGM_SOURCE_SEQ NOT IN (SELECT DISTINCT BGM_SOURCE_SEQ FROM BGM_REQUEST_EXPENSE WHERE BGM_REQ_SEQ = @seq)"
                + " ORDER BY BGM_SOURCE_SEQ";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", reqSeq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);
                
                return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

        }

        //public DataSet GetRequestBySeq(int seq)
        //{
        //    string strSQL;

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(1);

        //        dbManager.AddParameters(0, "@seq", seq);

        //        strSQL = "SELECT BGM_REQ_SEQ, BGM_REQ_CODE, BGM_REQ_NAME, BGM_REQ_DESCR, BGM_STRATEGY_SEQ AS BGM_STRAT_NAME, JOB_SITE_SEQ AS JOB_SITE_NAME"
        //                + ", BGM_REQ_REASON, BGM_REQ_OBJECT, BGM_REQ_TARGET, BGM_REQ_PLAN, BGM_REQ_REMARK, TOTAL_BUDGET"
        //            + " FROM BGM_REQUEST"
        //            + " WHERE BGM_REQ_SEQ = @seq";

        //        return dbManager.ExecuteDataSet(CommandType.Text, strSQL);
        //    }
        //    catch
        //    {
        //        return null;
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }
        //}

        public int Insert(RequestInfo reqInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(17);

                //dbManager.AddParameters(0, "@code", reqInfo.ReqCode);
                //dbManager.AddParameters(1, "@name", reqInfo.ReqName);
                //dbManager.AddParameters(2, "@descr", reqInfo.ReqDescr);
                //dbManager.AddParameters(3, "@date", reqInfo.ReqDate);
                //dbManager.AddParameters(4, "@dateStart", reqInfo.ReqDateStart);
                //dbManager.AddParameters(5, "@dateEnd", reqInfo.ReqDateEnd);
                //dbManager.AddParameters(6, "@reason", reqInfo.ReqReason);
                //dbManager.AddParameters(7, "@objects", reqInfo.ReqObject);
                //dbManager.AddParameters(8, "@target", reqInfo.ReqTarget);
                //dbManager.AddParameters(9, "@plan", reqInfo.ReqPlan);
                //dbManager.AddParameters(10, "@descr1", reqInfo.ReqDtls_1);
                //dbManager.AddParameters(11, "@remark", reqInfo.ReqRemark);
                //dbManager.AddParameters(12, "@strat_seq", reqInfo.ReqStrategy);
                //dbManager.AddParameters(13, "@job_site_seq", reqInfo.ReqJobSite);
                //dbManager.AddParameters(14, "@bgm_year", reqInfo.BgYear);
                //dbManager.AddParameters(15, "@status", reqInfo.RecordStatus);
                //dbManager.AddParameters(16, "@created_by", 0);

                strSQL = "INSERT INTO BGM_REQUEST(BGM_REQ_CODE, BGM_REQ_NAME, BGM_REQ_DESCR, BGM_REQ_DATE"
                        + ", BGM_REQ_MONTH_START, BGM_REQ_MONTH_END, BGM_REQ_REASON, BGM_REQ_OBJECT"
                        + ", BGM_REQ_TARGET, BGM_REQ_PLAN, BGM_REQ_REMARK, BGM_REQ_DTLS_1, BGM_STRATEGY_SEQ"
                        + ", JOB_SITE_SEQ, BGM_YEAR, RECORD_STATUS, CREATED_BY)"
                    + " VALUES(@code, @name, @descr, @date"
                        + ", @dateStart, @dateEnd, @reason, @objects, @target, @plan, @remark, @descr1, @strat_seq"
                        + ", @job_site_seq, @bgm_year, @status, @created_by)";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int Update(RequestInfo reqInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(19);

                //dbManager.AddParameters(0, "@seq", reqInfo.ReqSeq);
                //dbManager.AddParameters(1, "@code", reqInfo.ReqCode);
                //dbManager.AddParameters(2, "@name", reqInfo.ReqName);
                //dbManager.AddParameters(3, "@descr", reqInfo.ReqDescr);
                //dbManager.AddParameters(4, "@date", reqInfo.ReqDate);
                //dbManager.AddParameters(5, "@dateStart", reqInfo.ReqDateStart);
                //dbManager.AddParameters(6, "@dateEnd", reqInfo.ReqDateEnd);
                //dbManager.AddParameters(7, "@reason", reqInfo.ReqReason);
                //dbManager.AddParameters(8, "@objects", reqInfo.ReqObject);
                //dbManager.AddParameters(9, "@target", reqInfo.ReqTarget);
                //dbManager.AddParameters(10, "@plan", reqInfo.ReqPlan);
                //dbManager.AddParameters(11, "@descr1", reqInfo.ReqDtls_1);
                //dbManager.AddParameters(12, "@remark", reqInfo.ReqRemark);
                //dbManager.AddParameters(13, "@strat_seq", reqInfo.ReqStrategy);
                //dbManager.AddParameters(14, "@job_site_seq", reqInfo.ReqJobSite);
                //dbManager.AddParameters(15, "@bgm_year", reqInfo.BgYear);
                //dbManager.AddParameters(16, "@status", reqInfo.RecordStatus);
                //dbManager.AddParameters(17, "@updated_by", 0);
                //dbManager.AddParameters(18, "@updated_when", DateTime.Now);

                strSQL = "UPDATE BGM_REQUEST"
                        + " SET BGM_REQ_CODE = @code"
                            + ", BGM_REQ_NAME = @name"
                            + ", BGM_REQ_DESCR = @descr"
                            + ", BGM_REQ_MONTH_START = @dateStart"
                            + ", BGM_REQ_MONTH_END = @dateEnd"
                            + ", BGM_REQ_REASON = @reason"
                            + ", BGM_REQ_OBJECT = @objects"
                            + ", BGM_REQ_TARGET = @target"
                            + ", BGM_REQ_PLAN = @plan"
                            + ", BGM_REQ_REMARK = @remark"
                            + ", BGM_REQ_DTLS_1 = @descr1"
                            + ", BGM_STRATEGY_SEQ = @strat_seq"
                            + ", JOB_SITE_SEQ = @job_site_seq"
                            + ", RECORD_STATUS = @status"
                            + ", UPDATED_BY = @updated_by"
                            + ", UPDATED_WHEN = @updated_when"
                    + " WHERE BGM_REQ_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public int Delete(RequestInfo reqInfo)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                //dbManager.AddParameters(0, "@seq", reqInfo.ReqSeq);
                //dbManager.AddParameters(1, "@status", reqInfo.RecordStatus);
                //dbManager.AddParameters(2, "@updated_by", 0);
                //dbManager.AddParameters(3, "@updated_when", DateTime.Now);

                strSQL = "UPDATE BGM_REQUEST"
                    + " SET RECORD_STATUS = @status"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE BGM_REQ_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
